package utility.database;

import constant.FileLocation;
import entity.geometry.POI;
import entity.geometry.Point;
import org.geotools.util.MapEntry;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import utility.file.PropertiesLoader;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

/**
 * 对数据库中一些静态表进行预处理，将表中数据读取到本地，避免频繁查询数据库
 * @author lit
 * @date  2018/3/23.
 */
public class PreprocessPOIData {
    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private SqlServerAccess mssql = null;


    private boolean initMSSQL(){
        boolean result = true;
        if( mssql == null ){
            try {
                mssql = new SqlServerAccess();
            }catch (Exception e){
                e.printStackTrace();
                result = false;
            }
        }
        if( connection == null ){
            try {
                connection = mssql.getInstance();
                connection.setAutoCommit(false);
            } catch (Exception e) {
                e.printStackTrace();
                result = false;
            }
        }
        return result;
    }

    /**
     * 0.预处理POI数据表,从数据库中读取数据表数据存入本地文件中
     * @return
     */
    public int loadBJPOIData() {
        int poiCount = 0;
        if(initMSSQL()){
            ResultSet resultSet = null;
            List<POI> poiList = new ArrayList<>();
            String sql = "select POIID,POI_X,POI_Y from IDBox_Base.dbo.BJ_POI_2014";

            String filePath = PropertiesLoader.getParameter(FileLocation.PREPROCESS_FILE_KEY,FileLocation.PREPROCESS_FILE_DEFAULT);
            DataOutputStream out = null;

            try {
                out = new DataOutputStream(
                        new FileOutputStream(new File(filePath), false)
                );
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
                while(resultSet.next()){
                    String poiid = resultSet.getString("POIID") ;

                    double x = resultSet.getDouble("POI_X") ;
                    double y = resultSet.getDouble("POI_Y") ;
                    out.writeDouble(x);
                    out.writeDouble(y);

                    out.writeInt(poiid.length());
                    byte[] data = poiid.getBytes();
                    out.write(data, 0, poiid.length());
                    poiCount++;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }finally {
                if( out != null ){
                    try{
                        out.close();
                    }catch (Exception e){
                        e.printStackTrace();
                    }
                }
                SqlServerAccess.closeConnection(connection,preparedStatement,resultSet);
            }
        }
        return poiCount;
    }


    /**
     * 1.读取之前存入的POI文件
     * @return
     */
    public ArrayList<POI> loadPOIFile(){
        String filePath = PropertiesLoader.getParameter(FileLocation.PREPROCESS_FILE_KEY,FileLocation.PREPROCESS_FILE_DEFAULT);
        File file = new File(filePath);
        if(!file.exists()){
            loadBJPOIData();
        }
        ArrayList<POI> result = new ArrayList<>();

        try{
            DataInputStream in = new DataInputStream(new FileInputStream(filePath));
            while( in.available() > 0 ){
                double x = in.readDouble();
                double y = in.readDouble();
                int len = in.readInt();
                byte[] tmp = new byte[len];
                in.read(tmp, 0, len);
                String poiid = new String(tmp);
                POI poi = new POI();
                poi.setPoi_x(x);
                poi.setPoi_y(y);

                poi.setPoiid(poiid);
                result.add(poi);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 2.序列化poi坐标
     * @return
     */
    public ArrayList<Map<Long,HashSet<POI>>> generateIndex(ArrayList<POI> poiArrayList){
        Map<Long,HashSet<POI>> xIndexMap = new HashMap<>();
        Map<Long,HashSet<POI>> yIndexMap = new HashMap<>();

        for(POI poi : poiArrayList){
            double x = poi.getPoi_x();
            double y = poi.getPoi_y();
            long xIndex = (long)Math.floor((x - 0)/0.005);
            long yIndex = (long)Math.floor((y - 0)/0.005);

            if(xIndexMap.containsKey(xIndex)){
                xIndexMap.get(xIndex).add(poi);
            }else{
                HashSet<POI> xIndexSet = new HashSet<>();
                xIndexSet.add(poi);
                xIndexMap.put(xIndex,xIndexSet);
            }

            if(yIndexMap.containsKey(yIndex)){
                yIndexMap.get(yIndex).add(poi);
            }else{
                HashSet<POI> yIndexSet = new HashSet<>();
                yIndexSet.add(poi);
                yIndexMap.put(yIndex,yIndexSet);
            }
        }
        ArrayList<Map<Long,HashSet<POI>>> result = new ArrayList<>(2);
        result.add(0,xIndexMap);
        result.add(1,yIndexMap);
        return result;
    }

    /**
     * 获取离point点按照从近到远排序的备选POI
     * @param poiArray
     * @param point
     * @return
     */
    public TreeMap getPoi(ArrayList<Map<Long,HashSet<POI>>> poiArray, Point point){
        Logger logger = LoggerFactory.getLogger(PreprocessPOIData.class);

        double lon = point.getLon();
        double lat = point.getLat();

        POI result = new POI();
        Map<Long,HashSet<POI>> xIndexMap = poiArray.get(0);
        Map<Long,HashSet<POI>> yIndexMap = poiArray.get(1);

        long lonIndex = (long)Math.floor((lon - 0)/0.005);
        long latIndex = (long)Math.floor((lat - 0)/0.005);

        long startTime = System.currentTimeMillis();
        HashSet<POI> lonPOISet = new HashSet<>();
        HashSet<POI> lonPOI1 = xIndexMap.get(lonIndex);
        HashSet<POI> lonPOI2 = xIndexMap.get(lonIndex + 1);
        HashSet<POI> lonPOI3 = xIndexMap.get(lonIndex - 1);
        if(lonPOI1 != null){
            lonPOISet.addAll(lonPOI1);
        }
        if(lonPOI2 != null){
            lonPOISet.addAll(lonPOI2);
        }
        if(lonPOI3 != null){
            lonPOISet.addAll(lonPOI3);
        }

        HashSet<POI> latPOISet = new HashSet<>();
        HashSet<POI> latPOI1 = yIndexMap.get(latIndex);
        HashSet<POI> latPOI2 = yIndexMap.get(latIndex + 1);
        HashSet<POI> latPOI3 = yIndexMap.get(latIndex - 1);
        if(latPOI1 != null){
            latPOISet.addAll(latPOI1);
        }
        if(latPOI2 != null){
            latPOISet.addAll(latPOI2);
        }
        if(latPOI3 != null){
            latPOISet.addAll(latPOI3);
        }
        long endTime = System.currentTimeMillis();
        logger.debug("Save lon and lat array consume {}ms",endTime-startTime);

        startTime = System.currentTimeMillis();
        //取交集，获取备选POI点
        if(lonPOISet == null || latPOISet == null){
            return null;
        }
        lonPOISet.retainAll(latPOISet);
        endTime = System.currentTimeMillis();
        logger.debug("Get lon and lat array intersection consume {}ms",endTime-startTime);

        startTime = System.currentTimeMillis();
        double poix=0, poiy=0;
        //筛选POI,去除不符合条件的POI
        HashSet<POI> alternativePOI = new HashSet<POI>();
        for( POI poiAlternative : lonPOISet ){
            poix = poiAlternative.getPoi_x();
            poiy = poiAlternative.getPoi_y();
            if(poix <= lon + 0.0025 && poix >= lon - 0.0025 && poiy <= lat + 0.0025 && poiy >= lat - 0.0025){
                alternativePOI.add(poiAlternative);
            }
        }
        endTime = System.currentTimeMillis();
        logger.debug("Delete inappropriate element from array consume {}ms",endTime-startTime);

        startTime = System.currentTimeMillis();
        TreeMap resultTree = new TreeMap();
        if(alternativePOI.size() > 0){
            /*//获取距离最近的POI
            double minDistance = 1E6;*/
            for( POI poiAlternative :  alternativePOI ){
                poix = poiAlternative.getPoi_x();
                poiy = poiAlternative.getPoi_y();
                double distance = (lat - poiy)*(lat - poiy)+(lon - poix)*(lon - poix);
                resultTree.put(distance,poiAlternative);
                /*if(distance <= minDistance){
                    minDistance = distance;
                    result = poiAlternative;
                }*/
            }
        }
        endTime = System.currentTimeMillis();
        logger.debug("Get nearest poi consume {}ms",endTime-startTime);

        return resultTree;
    }
}
